package com.example.dao;

import com.example.domain.Novel;
import com.example.domain.User;
import com.example.utils.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.List;

//增删改查
public class UserDao {
    JdbcTemplate jdbcTemplate = null;
    User user = null;
    String sql = null;
    int affectedRows = 0;
//    根据用户Id查询查询用户
    public User getUserById(int id)
    {
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "select * from user where user_id=?";
            user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), id);
            return user;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
//    根据账号和密码查询用户
    public User getUserByUsernameAndPassword(String username, String password){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "select * from user where username=? and password=?";
            user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), username, password);
            return user;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

//    添加用户
    public int addUser(User user)
    {
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "insert into user(username,password,email,isadmin) values(?,?,?,?)";
            affectedRows = jdbcTemplate.update(sql,user.getUsername(),user.getPassword(),user.getEmail(),0);
            return affectedRows;
        }catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }
    public int adminAddUser(User user)
    {
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            int isAdmin = 0;
            if(user.isIsAdmin()){
                isAdmin = 1;
            }
            sql = "insert into user(username,password,email,isadmin) values(?,?,?,?)";
            affectedRows = jdbcTemplate.update(sql,user.getUsername(),user.getPassword(),user.getEmail(),isAdmin);
            return affectedRows;
        }catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }
//    检查用户名是否存在于数据库中
    public boolean checkUsername(String username)
    {
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "select * from user where username=?";
            user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), username);
            if(user.getUsername().equals(username)) {
                return true;
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }

    //检查电子邮件是否存在数据库中
    public boolean checkEmail(String email){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "select * from user where email=?";
            user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), email);
            if(user.getEmail().equals(email)) {
                return true;
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }
//    更新用户的信息
    public int updateUserInfo(User user)
    {
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "update user set username=?,password=?,email=? where user_id=?";
            affectedRows = jdbcTemplate.update(sql,user.getUsername(),user.getPassword(),user.getEmail(),user.getUser_id());
        }catch (Exception e){
            e.printStackTrace();
        }
        return affectedRows;
    }
    public int adminUpdateUserInfo(User user)
    {
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            int isAdmin = 0;
            if (user.isIsAdmin()){
                isAdmin = 1;
            }
            sql = "update user set username=?,password=?,email=?,isAdmin=? where user_id=?";
            affectedRows = jdbcTemplate.update(sql,user.getUsername(),user.getPassword(),user.getEmail(),isAdmin,user.getUser_id());
        }catch (Exception e){
            e.printStackTrace();
        }
        return affectedRows;
    }
//    获取用户数量
    public int getUserCount()
    {
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "select count(*) from user";
            return jdbcTemplate.queryForObject(sql,Integer.class);
        }catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }
//    根据用户ID删除用户ID
    public int deleteUserById(int id)
    {
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "delete from user where user_id=?";
//            并返回受影响的行数
            affectedRows = jdbcTemplate.update(sql,id);
        }catch (Exception e){
            e.printStackTrace();
        }
        return affectedRows;
    }
//    分页查询用户列表
    public List<User> getUserListByPage(int pageNo, int pageSize)
    {
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            int offset = (pageNo - 1) * pageSize; // 计算SQL查询的OFFSET值
            sql = "select * from user ORDER BY user_id LIMIT ? OFFSET ?";
            List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class), pageSize,offset);
            return userList;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
//    忘记密码时，根据用户名查询用户并修改密码
    public int forgetPassword(String username,String password)
    {
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "update user set password=? where username=?";
            affectedRows = jdbcTemplate.update(sql,password,username);
        }catch (Exception e){
            e.printStackTrace();
        }
        return affectedRows;
    }
//    通过用户名，邮箱查询用户
    public List<User> getUserBySearchCount(String keyword) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            // 构建 SQL 查询，搜索标题和作者中包含关键字的记录
            String sql = "SELECT * FROM user WHERE username LIKE ? OR email LIKE ?";
            // 使用 jdbcTemplate.query 执行查询
            // 传递关键字的 SQL 占位符，一个用于 title，一个用于 author
            return jdbcTemplate.query(sql, new Object[]{"%"+keyword + "%", "%"+keyword + "%"}, new BeanPropertyRowMapper<>(User.class));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}


